Tag Info

New answers tagged

0

the script I use will show you all objects (SP, Tables, Functions) for a specified Database name and all the users that have rights on them, but you can narrow the search and extract exactly what you need. DECLARE @EXEC_SCRIPT NVARCHAR(4000) DECLARE @DBNAME NVARCHAR(256) = 'DB name' SET @EXEC_SCRIPT = 'USE ['+ @DBNAME + '] select DB_NAME () ...


0

I wrote a script a while ago, that does just this. I've posted it on my blog, http://sqlsunday.com/downloads/. Remember that a Windows user can be a member of a Windows group, and in SQL Server, you can't see those memberships, so you'll have to look at Windows users and Windows groups separately, if you're doing a security audit, for instance. If you want ...


0

Off the top of my head (no MSSQL boxes up at the moment, sorry): EXECUTE AS <database user> ; SELECT * FROM sys.objects WHERE type = 'u' ; Metadata visibility means that if a database user queries sys.objects then they will only see the objects they have permissions to access or that they own. If you require the actual permissions they have on ...


1

Let's say the new default you want is 5: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'ALTER TABLE ' + t + ' DROP CONSTRAINT ' + c + '; ALTER TABLE ' + t + ' ADD CONSTRAINT ' + c + ' DEFAULT (5) FOR [project_id];' FROM ( SELECT t = QUOTENAME(s.name) + '.' + QUOTENAME(t.name), c = QUOTENAME(d.name) FROM sys.tables AS t INNER JOIN ...


0

By running this: select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME = 'project_id' you will get a list of all the tables that contain columns of that name. From there you can construct SQL using much the same query: select 'ALTER TABLE ' + TABLE_SCHEMA + '.' + TABLE_NAME ..... etc. to construct the required ALTER statements. Harvest the ...



Top 50 recent answers are included